import os
import time
from multiprocessing import Pool

import pyodbc
from dbutils.pooled_db import PooledDB


class DBPool():
    Pool = None

    # def __init__(self): self.Pool = Pool
    # def __del__(self): self.Pool.close()

    @staticmethod
    def mssql_pool_3326(database='db_name'):
        DBPool.Pool = PooledDB(pyodbc, mincached=65, SERVER="0.0.0.0", UID='user', PWD='', DATABASE=database, port=1433, charset="utf8", DRIVER='sql server')
        print(DBPool.Pool)

    @staticmethod
    def mssql_pool_25():
        DBPool.Pool = PooledDB(pyodbc, mincached=5, SERVER='0.0.0.0', UID='sa', PWD='pw', DATABASE='db', port=1433, charset='cp936', DRIVER='sql server')

    @staticmethod
    def connect():
        if DBPool.Pool is None:
            print('Pool is none')
            exit(1)

        conn = DBPool.Pool.connection()
        # cursor = conn.cursor()
        return conn

    @staticmethod
    def get(conn, sql, pnt=True):
        if pnt: print(sql)
        cursor = conn.cursor()
        cursor.execute(sql)
        # <class 'tuple'> (,)
        return cursor.fetchall()

    @staticmethod
    def set(conn, sql, pnt=True):
        if pnt: print(sql)
        cursor = conn.cursor()
        cursor.execute(sql)
        conn.commit()
        return cursor.rowcount


def handle(n):
    last_time = time.time()
    print(DBPool.get(DBPool.connect(), f'SELECT top {n}  * FROM 保单状态 '))
    print(f'{os.getpid()} use {int(time.time() - last_time)}s\n')


if __name__ == '__main__':
    print('run..')
    last_time = time.time()
    DBPool.mssql_pool_25()

    top_len = 5

    pool = Pool(processes=32)

    for i in range(top_len): pool.apply_async(handle, args=(i,))

    pool.close()
    pool.join()

    print(f'all use {int(time.time() - last_time)}s')
    print('..run')
